package com.xinqi.modules.stats.utils;

import com.xinqi.common.base.error.CommonErrors;
import com.xinqi.common.core.constant.Constants;
import com.xinqi.common.core.utils.MessageUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class ExcelUtil {

    public static XSSFWorkbook createExcel(String title, String description,List<Map<String,Object>> titleList, List<Map<String,Object>> infoList) {
        try{
            //新建工作簿
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

            //新建工作表
            XSSFSheet sheet = xssfWorkbook.createSheet(description);
            //设置单元格宽度
            sheet.setDefaultColumnWidth(20);

            //第一行合并人数
            CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, titleList.size() + 1);
            //添加要合并地址到表格
            sheet.addMergedRegion(rangeAddress);
            //创建行，指定起始行号，从0开始
            XSSFRow row = sheet.createRow(0);
            row.setHeightInPoints(50);
            //创建单元格，指定起始列号，从0开始
            XSSFCell cell = row.createCell(0);
            //设置单元格内容
            cell.setCellValue(title);
            //创建样式对象
            CellStyle style = xssfWorkbook.createCellStyle();
            //设置样式对齐方式：水平垂直居中
            setFontSize(xssfWorkbook, style, (short) Font.COLOR_NORMAL,24, true);
            //设置样式对齐方式：水平垂直居中
            setHV(style, cell);

            //第二行合并单元格
            CellRangeAddress range1Address = new CellRangeAddress(1, 1, 0, titleList.size() + 1);
            //添加要合并地址到表格
            sheet.addMergedRegion(range1Address);
            //创建行，指定起始行号，从0开始
            XSSFRow rowDsc = sheet.createRow(1);
            rowDsc.setHeightInPoints(36);
            //创建单元格，指定起始列号，从0开始
            XSSFCell cellDsc = rowDsc.createCell(0);
            //设置单元格内容
            cellDsc.setCellValue(description);
            //创建样式对象
            CellStyle style1 = xssfWorkbook.createCellStyle();
            //设置样式对齐方式：水平垂直居中
            setFontSize(xssfWorkbook, style1, (short) Font.COLOR_NORMAL,20, true);
            //设置样式对齐方式：水平垂直居中
            setHV(style1, cellDsc);

            //设置标题
            setTitle(xssfWorkbook, sheet, titleList);

            //设置内容
            setValue(xssfWorkbook, sheet, titleList, infoList);

            return xssfWorkbook;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    // 设置字体大小
    private static void setFontSize(XSSFWorkbook workbook, CellStyle style, short color, int size, boolean boldFlag) {
        try {
            XSSFFont font = workbook.createFont();
            //颜色
            font.setColor(color);
            //设置字体大小
            font.setFontHeightInPoints((short) size);
            font.setBold(boldFlag);
            //字体
            style.setFont(font);
        } catch (Exception e) { }
    }

    // 水平垂直剧中
    private static void setHV(CellStyle style, XSSFCell cell) {
        //设置样式对齐方式：水平垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //为指定单元格设定样式
        cell.setCellStyle(style);
    }

    // 设置标题
    private static void setTitle(XSSFWorkbook workbook, XSSFSheet sheet, List<Map<String,Object>> titleList) {
        //创建行，指定起始行号，从2开始
        XSSFRow row = sheet.createRow(2);
        row.setHeightInPoints(20);
        for (int i = 0; i< titleList.size() + 2; i ++) {
            //创建单元格，指定起始列号，从0开始
            XSSFCell cell = row.createCell(i);
            //创建样式对象
            CellStyle style = workbook.createCellStyle();
            if (i == 0) {
                cell.setCellValue("姓名");
                //设置样式对齐方式：水平垂直居中
                setFontSize(workbook, style, (short) Font.COLOR_NORMAL,14, true);
            } else if (i == 1) {
                cell.setCellValue("学号");
                //设置样式对齐方式：水平垂直居中
                setFontSize(workbook, style, (short) Font.COLOR_NORMAL,14, true);
            } else {
                Map<String,Object> map = (Map<String,Object>) titleList.get(i - 2);
                cell.setCellValue(map.get("title").toString());
                //设置样式对齐方式：水平垂直居中
                setFontSize(workbook, style, (short) IndexedColors.LIGHT_BLUE.getIndex(),14, true);
            }
            //设置样式对齐方式：水平垂直居中
            setHV(style, cell);
        }
    }

    // 设置内容
    private static void setValue(XSSFWorkbook workbook, XSSFSheet sheet, List<Map<String,Object>> titleList, List<Map<String,Object>> list) {
        for (int i = 0; i < list.size(); i ++) {
            Map<String,Object> map = (Map<String, Object>) list.get(i);
            //创建行，指定起始行号，从2开始
            XSSFRow row = sheet.createRow(i + 3);
            row.setHeightInPoints(20);
            for (int j = 0; j< titleList.size() + 2; j ++) {
                //创建单元格，指定起始列号，从0开始
                XSSFCell cell = row.createCell(j);
                //创建样式对象
                CellStyle style = workbook.createCellStyle();
                //设置字体样式
                setFontSize(workbook, style, (short) Font.COLOR_NORMAL,12, false);
                //设置样式对齐方式：水平垂直居中
                setHV(style, cell);
                if (j == 0) {
                    cell.setCellValue(map.get("nickname").toString());
                } else if (j == 1) {
                    cell.setCellValue(map.get("student_number")==null?"":map.get("student_number").toString());
                } else {
                    Map<String,Object> titleMap = (Map<String,Object>) titleList.get(j - 2);
                    String unionValue= titleMap.get("unionValue").toString();
                    cell.setCellValue(map.get(unionValue).toString() + " 分");
                }

            }
        }
    }

    /**
     * @param fileName 下载的文件名
     * @param response 响应
     * @param wb       excel工作簿对象
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, XSSFWorkbook wb) {
        try {
            OutputStream outputStream = response.getOutputStream();
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso8859-1") + ".xlsx");
            response.setHeader("filename",fileName.getBytes() + ".xlsx");
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取文件列表列数
     * @param path 文件路径
     * @param minLength 最小列数
     * @param maxLength 最大列数
     */
    public static void checkExcelCellNum(String path, Integer minLength, Integer maxLength) {
        InputStream inputStream = null;
        OPCPackage opcPackage = null;
        Workbook workbook = null;
        try {
            // 文件的URL地址
            URL url = new URL(path);
            // 获取文件的输入流
            inputStream = url.openStream();

            if (path.endsWith(".xlsx")) {
                opcPackage = OPCPackage.open(inputStream);
                workbook = new XSSFWorkbook(opcPackage);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
            int cellNum = 0;
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                for (Row row : sheet) {
                    // 获取当前行的实际列数
                    int columnCount = row.getPhysicalNumberOfCells();
                    if (columnCount > cellNum) {
                        cellNum = columnCount;
                    }
                }
            }
            if (Objects.nonNull(workbook)) {
                workbook.close();
            }
            if (Objects.nonNull(opcPackage)) {
                opcPackage.close();
            }
            if (Objects.nonNull(inputStream)) {
                inputStream.close();
            }

            if (cellNum < minLength) {
                CommonErrors.BAD_REQUEST.check(false, MessageUtils.message("upload.filename.cell.max.length.valid"));
            }
            if (cellNum > maxLength) {
                CommonErrors.BAD_REQUEST.check(false, MessageUtils.message("upload.filename.cell.min.length.valid"));
            }
        } catch (Exception e) {
            CommonErrors.BAD_REQUEST.check(false, "文件格式错误");
        }
    }

    public static boolean checkExcelFile(String path) {
        int lastIndex = path.lastIndexOf(".");
        if (lastIndex != -1) {
            String extension = path.substring(lastIndex + 1).toLowerCase();
            if (Objects.equals(Constants.XLS_SUF, extension)
                || Objects.equals(Constants.XLSX_SUF, extension)) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    /**
     * @param fileName 下载的文件名
     * @param response 响应
     * @param wb       excel工作簿对象
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook wb) {
        try {
            OutputStream outputStream = response.getOutputStream();
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso8859-1") + ".xlsx");
            response.setHeader("filename",fileName.getBytes() + ".xlsx");
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 全边框
    public static CellStyle allFrame(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

}
